﻿Imports System.Data
Imports ADOEntityKYC
Imports ADOEntityAMLO1059
Imports ADOEntity
Imports DevExpress.Web.ASPxUploadControl
Imports System.IO
Imports System.Data.OleDb

Partial Class AMLO_Amlo1_05_9
    Inherits Web.UI.BasePage
    Private Const strMenuCode As String = "M57"
    Dim dsAmlo As DataSet
    Dim clsDatabase As New clsDatabase
    Dim clsDataTable As clsLINQToDataTable
    Dim AMLOEntity As AMLO1059Entities
    Dim intBeforeItemIndex As Integer
    Dim clsSessionMenuRight As clsSessionMenuRight
    Dim clsFormatDate As clsDateFormat
    Dim clsMasterDropDown As clsMasterDropDown

#Region "Even From "
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        chkIsLogin()
        btnUpload.Attributes.Add("onclick", "NoDoubleSubmit();")
        If (Not IsPostBack) Then

            AMLOEntity = New AMLO1059Entities
            clsMasterDropDown = New clsMasterDropDown
            clsSessionMenuRight = Session("ClsMenuRight")
            dsAmlo = New DataSet()
            CreateControlMP()
            setUserRightButton()

            AMLOEntity.Dispose()
            AMLOEntity = Nothing
        Else
            dsAmlo = Session("ds")
            grdAmlo.DataSource = dsAmlo.Tables("Amlo")
            grdAmlo.DataBind()
        End If

    End Sub

    Protected Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Unload
        Session("ds") = dsAmlo
    End Sub
#End Region

#Region "SetForm"

    Sub resourceSet()

    End Sub
#End Region

#Region "Even Button"
    'Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click
    '    IsDataBind()
    '    SaveLogSearch()
    '    clearGvIndex()
    'End Sub

    Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
        If (fileUpload1.HasFile Or fileUpload2.HasFile Or fileUpload3.HasFile) Then

            If (ValidateFileUpload(fileUpload1) AndAlso ValidateFileUpload(fileUpload2) AndAlso ValidateFileUpload(fileUpload3)) Then
                SavePostedFile()
                IsDataBind()
            End If
        Else
            ScriptManager.RegisterStartupScript(Me, _
                                                    Me.GetType(), _
                                                    "clientScript", _
                                                    "alert('กรุณาเลือกไฟล์ที่ต้องการอัพโหลดอย่างน้อย 1 รายการ')", _
                                                    True)
        End If

    End Sub


    'Protected Sub btnEdit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnEdit.Click

    '    If Not (grdAmlo.SelectedIndex = -1) Then
    '        Dim txtCode As String = CType(grdAmlo.Rows(grdAmlo.SelectedIndex).FindControl("hdfRptSeq"), HiddenField).Value
    '        SaveLogPrint(1)
    '        Response.Redirect("Amlo1_03.aspx?status=Edit&code=" + txtCode)

    '    Else
    '        ScriptManager.RegisterStartupScript(UpdatePanel1, UpdatePanel1.GetType(), "clientScript", "alert('" + Resources.resValidate.nonSelectGrid + "')", True)

    '    End If
    'End Sub

    Protected Sub btnCancel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Response.Redirect("Amlo1_05_9.aspx")
    End Sub

#End Region

#Region "Even GridView"


#End Region

#Region " Function "

    Private Sub IsDataBind()
        Try
            Dim sql = "select t.p3_trans_date, count(*) as total" _
                    & " from " & System.Configuration.ConfigurationSettings.AppSettings("TableAMLO1059") & " t" _
                    & " where t.createdate > TRUNC(SYSDATE)" _
                    & " group by t.p3_trans_date"

            clsDatabase = New clsDatabase
            Dim dt = clsDatabase.QueryDataTable(sql)

            grdAmlo.DataSource = dt
            grdAmlo.DataBind()

        Catch ex As Exception

        End Try
    End Sub
    'Private Sub SaveLogPrint(ByVal index As Integer)
    '    Try
    '        Dim program As String = strMenuCode
    '        Dim desc As String = ""

    '        clsSessionMenuRight = Session("ClsMenuRight")

    '        If index = 1 Then
    '            desc = "ค้นหาข้อมูลจาก "
    '            If Not (ddlBranch.SelectedIndex = 0) Then
    '                desc = desc & " ส่วนงาน/สาขา " & ddlBranch.Text
    '            End If


    '            If Not (txtYear.Text = "") Then
    '                desc = desc & " ปี " & txtYear.Text
    '            End If
    '            If Not (txtNo.Text = "") Then
    '                desc = desc & " เลขที่ " & txtNo.Text
    '            End If

    '            If Not (txtDateStart.Text = "" And txtDateEnd.Text = "") Then
    '                desc = desc & " วันที่ทำธุรกรรม " & txtDateStart.Text & " ถึง " & txtDateEnd.Text
    '            End If

    '            If Not (rdolIsComplete.SelectedValue = -1) Then
    '                desc = desc & " สถานะ " & rdolIsComplete.Text
    '            End If


    '        Else
    '            desc = "พิมพ์ฟอร์ม แบบรายงานการทำธุรกรรมที่ใช้เงินสด"
    '        End If

    '        Dim strFullNo As String = CType(grdAmlo.Rows(grdAmlo.SelectedIndex).FindControl("hdfRptFullNo"), HiddenField).Value


    '        Dim clsLogTransaction As New clsLogTransaction
    '        clsLogTransaction.saveLogTransaction(strFullNo.Trim, clsSessionMenuRight.getUserName, index, desc, program)
    '        clsLogTransaction = Nothing
    '    Catch ex As Exception
    '        Throw ex
    '    End Try
    'End Sub

    Private Sub setUserRightButton()
        Dim col As New Collection

        'col.Add(btnSearch, statusbtn.Query.ToString)
        col.Add(btnUpload, statusbtn.Add.ToString)
        'col.Add(btnEdit, statusbtn.Modify.ToString)
        'col.Add(btnDelete, statusbtn.delete.ToString)
        'col.Add(btnPrint, statusbtn.print.ToString)
        setBtnToUserRight(col, strMenuCode, clsSessionMenuRight.getDsMenuRight)

    End Sub

    'Public Sub setDefaultSearch()
    '    clsFormatDate = New clsDateFormat
    '    txtDateStart.Text = clsFormatDate.chkDateThOrEn(clsDateFormat.enmDateCultureInfo.Th, dteDateIsChk:=Date.Now)
    '    txtDateEnd.Text = clsFormatDate.chkDateThOrEn(clsDateFormat.enmDateCultureInfo.Th, dteDateIsChk:=Date.Now)
    '    txtYear.Text = clsFormatDate.GetYearLast(clsDateFormat.enmDateCultureInfo.Th).Substring(2, 2)


    '    If (clsSessionMenuRight.getRoleGroup > 79) Then
    '        rdoDelete.Visible = True
    '        ChkIsDelete.Visible = False
    '    ElseIf (clsSessionMenuRight.getRoleGroup < 80) Then
    '        rdoDelete.Visible = False
    '        ChkIsDelete.Visible = True
    '    End If

    '    If (clsSessionMenuRight.getRoleGroup > 59) Then
    '        ddlBranch.Enabled = True
    '    ElseIf (clsSessionMenuRight.getRoleGroup < 60) Then
    '        ddlBranch.Enabled = False
    '    End If


    '    rdolIsComplete.SelectedValue = -1
    '    rdoDelete.SelectedIndex = 0
    'End Sub

    Sub CreateControlMP() 'อ้าง control ใน masterPage
        Dim img As Image = CType(Master.FindControl("imgHead"), Image)
        img.ImageUrl = "~/img/ghb_.GIF"
        Dim lbl As Label = CType(Master.FindControl("lblPage"), Label)
        lbl.Text = "ปปง. 1-05-9 > Upload Excel Files ปปง.1-05-9"
    End Sub

    Private Function GetSortDirection(ByVal column As String) As String

        ' By default, set the sort direction to ascending.
        Dim sortDirection = "DESC"

        ' Retrieve the last column that was sorted.
        Dim sortExpression = TryCast(ViewState("SortExpression"), String)

        If sortExpression IsNot Nothing Then
            ' Check if the same column is being sorted.
            ' Otherwise, the default value can be returned.
            If sortExpression = column Then
                Dim lastDirection = TryCast(ViewState("SortDirection"), String)
                If lastDirection IsNot Nothing _
                  AndAlso lastDirection = "DESC" Then

                    sortDirection = "ASC"

                End If
            End If
        End If

        ' Save new values in ViewState.
        ViewState("SortDirection") = sortDirection
        ViewState("SortExpression") = column
        Return sortDirection
    End Function

#End Region

#Region "Event Upload"
    Private Const UploadDirectory As String = "C:/TestUpload/"

    Private Function ValidExtension(ByVal fileName As String) As Boolean
        Dim extension = Path.GetExtension(fileName)
        If (extension = ".xls" Or extension = ".xlsx") Then
            Return True
        End If
        Return False
    End Function

    Private Function ValidateFileUpload(ByVal fileUpload As FileUpload) As Boolean
        If (fileUpload.HasFile) Then
            If Not (ValidExtension(fileUpload.FileName)) Then
                ScriptManager.RegisterStartupScript(Me, _
                                                    Me.GetType(), _
                                                    "clientScript", _
                                                    "alert('กรุณาอัพโหลดด้วยไฟล์นามสกุล xls หรือ xlsx')", _
                                                    True)
                Return False
            End If
        End If
        Return True
    End Function

    Private Sub SavePostedFile()
        Dim savePath = Path.Combine(UploadDirectory, DateTime.Now.ToString("yyyyMMddHHmmss"))
        Dim fullFileName As String = ""

        Directory.CreateDirectory(savePath)

        Try
            If (fileUpload1.HasFile) Then
                fullFileName = Path.Combine(savePath, fileUpload1.FileName)
                fileUpload1.SaveAs(fullFileName)
                ImportFile1(fullFileName)
            End If
            If (fileUpload2.HasFile) Then
                fullFileName = Path.Combine(savePath, fileUpload2.FileName)
                fileUpload2.SaveAs(fullFileName)
                ImportFile2(fullFileName)
            End If
            If (fileUpload3.HasFile) Then
                fullFileName = Path.Combine(savePath, fileUpload3.FileName)
                fileUpload3.SaveAs(fullFileName)
                ImportFile3(fullFileName)
            End If
            
        Catch ex As Exception
            Dim message = ex.Message
            If (Not IsNothing(ex.InnerException)) Then
                message = ex.InnerException.Message
            End If

            ScriptManager.RegisterStartupScript(Me, _
                                                Me.GetType(), _
                                                "clientScript", _
                                                "alert('Error : " + message.Replace("'", "") + "')", _
                                                True)
        End Try
    End Sub

    Private Function ReadExcel(ByVal fileName As String) As DataTable
        Dim excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=""Excel 12.0;"""
        'Dim excelConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=""Excel 8.0;"""
        Dim dt = New DataTable()
        Try
            Using con As New OleDbConnection(excelConnectionString)
                con.Open()

                Dim tables = con.GetSchema("Tables")
                Dim sheetName = ""
                For i = 0 To tables.Rows.Count - 1
                    sheetName = tables(i)("TABLE_NAME")
                    If sheetName.EndsWith("$") Then
                        Exit For
                    End If
                Next
                'Dim sheetName = con.GetSchema("Tables").Rows(0)("TABLE_NAME")
                Dim cmd = New OleDbDataAdapter("select * from [" + sheetName + "]", con)
                cmd.Fill(dt)

                con.Close()
            End Using
        Catch
        End Try
        Return dt
    End Function

    Private Sub ImportFile1(ByVal fileName As String)
        Dim dt As DataTable = ReadExcel(fileName)
        AMLOEntity = New AMLO1059Entities
        Dim obj As AMLO1059
        clsSessionMenuRight = Session("ClsMenuRight")
        'Dim id As Integer = 1
        For Each row As DataRow In dt.Rows
            Dim init_sol_id = row("INIT_SOL_ID").ToString.Trim
            Dim p3_tran_id = row("P3_TRAN_ID").ToString.Trim
            Dim p3_tran_id2 = row("P3_TRAN_ID2").ToString.Trim
            Dim p3_trans_date = ConvertDate(row("P3_TRANS_DATE").ToString)
            Dim p3_foracid = row("P3_FORACID").ToString.Trim
            Dim p3_foracid2 = row("P3_FORACID2").ToString.Trim

            Dim qry = (From t In AMLOEntity.AMLO1059 Where t.INIT_SOL_ID = init_sol_id _
                   And t.P3_TRAN_ID = p3_tran_id And t.P3_TRAN_ID2 = p3_tran_id2 _
                   And t.P3_TRANS_DATE = p3_trans_date _
                   And (t.P3_FORACID = p3_foracid Or (String.IsNullOrEmpty(t.P3_FORACID) And String.IsNullOrEmpty(p3_foracid))) _
                   And t.P3_FORACID2 = p3_foracid2
                   Order By t.RPTREV Descending
                   ).ToList
            If (qry.Count > 0) Then
                If (qry(0).ISSEND = "1") Then
                    obj = New AMLO1059
                    obj.ID = GetAmlo1059Seq()
                    obj.RPTREV = qry(0).RPTREV + 1
                Else
                    obj = qry(0)
                End If
            Else
                obj = New AMLO1059
                obj.ID = GetAmlo1059Seq()
                obj.RPTREV = 0
            End If


            'obj = New AMLO1059
            'obj.ID = id
            obj.KEY_FLG = row("KEY_FLG").ToString
            obj.INIT_SOL_ID = init_sol_id
            obj.VALUE_TYPE = row("VALUE_TYPE").ToString
            obj.ANOTHER_METHOD = row("ANOTHER_METHOD").ToString
            obj.P2_FORACID2 = row("P2_FORACID2").ToString
            obj.P2_FLG_TYPE2 = row("P2_FLG_TYPE2").ToString
            obj.P2_SALUTATION2 = row("P2_SALUTATION2").ToString
            obj.P2_CUST_FNAME2 = row("P2_CUST_FNAME2").ToString
            obj.P2_CUST_MNAME2 = row("P2_CUST_MNAME2").ToString
            obj.P2_CUST_LNAME2 = row("P2_CUST_LNAME2").ToString
            obj.P2_NATIONALITY2 = row("P2_NATIONALITY2").ToString
            obj.P2_TYPE2_HOME = row("P2_TYPE2_HOME").ToString
            obj.P2_HOME2_NO = row("P2_HOME2_NO").ToString
            obj.P2_HOME2_ADDRESS = row("P2_HOME2_ADDRESS").ToString
            obj.P2_HOME2_CITY = row("P2_HOME2_CITY").ToString
            obj.P2_HOME2_STATE = row("P2_HOME2_STATE").ToString
            obj.P2_HOME2_COUNTRY = row("P2_HOME2_COUNTRY").ToString
            obj.P2_HOME2_ZIP = row("P2_HOME2_ZIP").ToString
            obj.P2_HOME2_NATION = row("P2_HOME2_NATION").ToString
            obj.P2_TYPE2_HPHONE = row("P2_TYPE2_HPHONE").ToString
            obj.P2_HOME2_PHONE = row("P2_HOME2_PHONE").ToString
            obj.P2_TYPE2_HFAX = row("P2_TYPE2_HFAX").ToString
            obj.P2_HOME2_FAX = row("P2_HOME2_FAX").ToString
            obj.P2_OCCUPATION2_CODE = ConvertInteger(row("P2_OCCUPATION2_CODE").ToString)
            obj.P2_OCCUPATION2_DESC = row("P2_OCCUPATION2_DESC").ToString
            obj.P2_TYPE2_WORK = row("P2_TYPE2_WORK").ToString
            obj.P2_WORK2_NO = row("P2_WORK2_NO").ToString
            obj.P2_WORK2_ADDRESS = row("P2_WORK2_ADDRESS").ToString
            obj.P2_WORK2_CITY = row("P2_WORK2_CITY").ToString
            obj.P2_WORK2_STATE = row("P2_WORK2_STATE").ToString
            obj.P2_WORK2_COUNTRY = row("P2_WORK2_COUNTRY").ToString
            obj.P2_WORK2_ZIP = row("P2_WORK2_ZIP").ToString
            obj.P2_WORK2_NATION = row("P2_WORK2_NATION").ToString
            obj.P2_TYPE2_WPHONE = row("P2_TYPE2_WPHONE").ToString
            obj.P2_WORK2_PHONE = row("P2_WORK2_PHONE").ToString
            obj.P2_TYPE2_WFAX = row("P2_TYPE2_WFAX").ToString
            obj.P2_WORK2_FAX = row("P2_WORK2_FAX").ToString
            obj.P2_TYPE2_MAIL = row("P2_TYPE2_MAIL").ToString
            obj.P2_MAIL2_NO = row("P2_MAIL2_NO").ToString
            obj.P2_MAIL2_ADDRESS = row("P2_MAIL2_ADDRESS").ToString
            obj.P2_MAIL2_CITY = row("P2_MAIL2_CITY").ToString
            obj.P2_MAIL2_STATE = row("P2_MAIL2_STATE").ToString
            obj.P2_MAIL2_COUNTRY = row("P2_MAIL2_COUNTRY").ToString
            obj.P2_MAIL2_ZIP = row("P2_MAIL2_ZIP").ToString
            obj.P2_MAIL2_NATION = row("P2_MAIL2_NATION").ToString
            obj.P2_TYPE2_MPHONE = row("P2_TYPE2_MPHONE").ToString
            obj.P2_MAIL2_PHONE = row("P2_MAIL2_PHONE").ToString
            obj.P2_TYPE2_MCELL = row("P2_TYPE2_MCELL").ToString
            obj.P2_MAIL2_CELL = row("P2_MAIL2_CELL").ToString
            obj.P2_TYPE2_MFAX = row("P2_TYPE2_MFAX").ToString
            obj.P2_MAIL2_FAX = row("P2_MAIL2_FAX").ToString
            obj.P2_TYPE2_MMAIL = row("P2_TYPE2_MMAIL").ToString
            obj.P2_MAIL2_EMAIL = row("P2_MAIL2_EMAIL").ToString
            obj.P2_CITIZEN_ID2 = row("P2_CITIZEN_ID2").ToString
            obj.P2_CITIZEN_OPN_BY2 = row("P2_CITIZEN_OPN_BY2").ToString
            If Not (row("P2_CITIZEN_OPN2").ToString.Trim = "") Then
                obj.P2_CITIZEN_OPN2 = ConvertDate(row("P2_CITIZEN_OPN2").ToString)
            End If
            If Not (row("P2_CITIZEN_EXPIRED2").ToString.Trim = "") Then
                obj.P2_CITIZEN_EXPIRED2 = ConvertDate(row("P2_CITIZEN_EXPIRED2").ToString)
            End If
            obj.P2_FLG_TYPE = row("P2_FLG_TYPE").ToString
            obj.P2_SALUTATION = row("P2_SALUTATION").ToString
            obj.P2_CUST_FNAME = row("P2_CUST_FNAME").ToString
            obj.P2_CUST_MNAME = row("P2_CUST_MNAME").ToString
            obj.P2_CUST_LNAME = row("P2_CUST_LNAME").ToString
            obj.P2_NATIONALITY = row("P2_NATIONALITY").ToString
            obj.P2_COMPANY_NAME = row("P2_COMPANY_NAME").ToString
            obj.P2_COMPANY_CODE = row("P2_COMPANY_CODE").ToString
            obj.P2_TYPE_HOME = row("P2_TYPE_HOME").ToString
            obj.P2_HOME_NO = row("P2_HOME_NO").ToString
            obj.P2_HOME_ADDRESS = row("P2_HOME_ADDRESS").ToString
            obj.P2_HOME_CITY = row("P2_HOME_CITY").ToString
            obj.P2_HOME_STATE = row("P2_HOME_STATE").ToString
            obj.P2_HOME_COUNTRY = row("P2_HOME_COUNTRY").ToString
            obj.P2_HOME_ZIP = row("P2_HOME_ZIP").ToString
            obj.P2_HOME_NATION = row("P2_HOME_NATION").ToString
            obj.P2_TYPE_HPHONE = row("P2_TYPE_HPHONE").ToString
            obj.P2_HOME_PHONE = row("P2_HOME_PHONE").ToString
            obj.P2_TYPE_HFAX = row("P2_TYPE_HFAX").ToString
            obj.P2_HOME_FAX = row("P2_HOME_FAX").ToString
            obj.P2_OCCUPATION_CODE = row("P2_OCCUPATION_CODE").ToString
            obj.P2_OCCUPATION_DESC = row("P2_OCCUPATION_DESC").ToString
            obj.P2_TYPE_WORK = row("P2_TYPE_WORK").ToString
            obj.P2_WORK_NO = row("P2_WORK_NO").ToString
            obj.P2_WORK_ADDRESS = row("P2_WORK_ADDRESS").ToString
            obj.P2_WORK_CITY = row("P2_WORK_CITY").ToString
            obj.P2_WORK_STATE = row("P2_WORK_STATE").ToString
            obj.P2_WORK_COUNTRY = row("P2_WORK_COUNTRY").ToString
            obj.P2_WORK_ZIP = row("P2_WORK_ZIP").ToString
            obj.P2_WORK_NATION = row("P2_WORK_NATION").ToString
            obj.P2_TYPE_WPHONE = row("P2_TYPE_WPHONE").ToString
            obj.P2_WORK_PHONE = row("P2_WORK_PHONE").ToString
            obj.P2_TYPE_WFAX = row("P2_TYPE_WFAX").ToString
            obj.P2_WORK_FAX = row("P2_WORK_FAX").ToString
            obj.P2_TYPE_MAIL = row("P2_TYPE_MAIL").ToString
            obj.P2_MAIL_NO = row("P2_MAIL_NO").ToString
            obj.P2_MAIL_ADDRESS = row("P2_MAIL_ADDRESS").ToString
            obj.P2_MAIL_CITY = row("P2_MAIL_CITY").ToString
            obj.P2_MAIL_STATE = row("P2_MAIL_STATE").ToString
            obj.P2_MAIL_COUNTRY = row("P2_MAIL_COUNTRY").ToString
            obj.P2_MAIL_ZIP = row("P2_MAIL_ZIP").ToString
            obj.P2_MAIL_NATION = row("P2_MAIL_NATION").ToString
            obj.P2_TYPE_MPHONE = row("P2_TYPE_MPHONE").ToString
            obj.P2_MAIL_PHONE = row("P2_MAIL_PHONE").ToString
            obj.P2_TYPE_MCELL = row("P2_TYPE_MCELL").ToString
            obj.P2_MAIL_CELL = row("P2_MAIL_CELL").ToString
            obj.P2_TYPE_MFAX = row("P2_TYPE_MFAX").ToString
            obj.P2_MAIL_FAX = row("P2_MAIL_FAX").ToString
            obj.P2_TYPE_MMAIL = row("P2_TYPE_MMAIL").ToString
            obj.P2_MAIL_EMAIL = row("P2_MAIL_EMAIL").ToString
            obj.P2_CITIZEN_ID = row("P2_CITIZEN_ID").ToString
            obj.P2_CITIZEN_OPN_BY = row("P2_CITIZEN_OPN_BY").ToString
            If Not (row("P2_CITIZEN_OPN").ToString.Trim = "") Then
                obj.P2_CITIZEN_OPN = ConvertDate(row("P2_CITIZEN_OPN").ToString)
            End If
            If Not (row("P2_CITIZEN_EXPIRED").ToString.Trim = "") Then
                obj.P2_CITIZEN_EXPIRED = ConvertDate(row("P2_CITIZEN_EXPIRED").ToString)
            End If
            obj.P3_TRANS_DATE = p3_trans_date
            obj.P3_TRANS_TYPE = row("P3_TRANS_TYPE").ToString
            obj.P3_TRAN_CODE = row("P3_TRAN_CODE").ToString
            obj.P3_TRAN_AMT = row("P3_TRAN_AMT").ToString
            obj.P3_THAI_BAHT = row("P3_THAI_BAHT").ToString
            obj.P3_FOREIN_MONEY = row("P3_FOREIN_MONEY").ToString
            obj.P3_MONEY_TYPE = row("P3_MONEY_TYPE").ToString
            obj.P3_RATE_EX = IIf(row("P3_RATE_EX").ToString = "NAV", Nothing, row("P3_RATE_EX").ToString)
            obj.P3_CUST_NAME = row("P3_CUST_NAME").ToString
            obj.P3_FORACID = p3_foracid
            obj.P3_TRAN_ID = p3_tran_id
            obj.P3_COMPANY_NAME = row("P3_COMPANY_NAME").ToString
            obj.P3_COUNTRY = row("P3_COUNTRY").ToString
            obj.P3_HOME_ADDRESS = row("P3_HOME_ADDRESS").ToString
            obj.P3_MAIL_ADDRESS = row("P3_MAIL_ADDRESS").ToString
            obj.P3_LICENSE = row("P3_LICENSE").ToString
            obj.P3_CITIZEN_ID = row("P3_CITIZEN_ID").ToString
            obj.P3_CITIZEN_BY = row("P3_CITIZEN_BY").ToString
            If Not (row("P3_CITIZEN_EXPIRED").ToString.Trim = "") Then
                obj.P3_CITIZEN_EXPIRED = ConvertDate(row("P3_CITIZEN_EXPIRED").ToString)
            End If
            If Not (row("P3_CUST_DOB").ToString.Trim = "") Then
                obj.P3_CUST_DOB = ConvertDate(row("P3_CUST_DOB").ToString)
            End If
            obj.P3_BORN_PLACE = row("P3_BORN_PLACE").ToString
            obj.P3_CUST_NAME2 = row("P3_CUST_NAME2").ToString
            obj.P3_FORACID2 = p3_foracid2
            obj.P3_TRAN_ID2 = p3_tran_id2
            obj.P3_COMPANY_NAME2 = row("P3_COMPANY_NAME2").ToString
            obj.P3_COUNTRY2 = row("P3_COUNTRY2").ToString
            obj.P3_HOME_ADDRESS2 = row("P3_HOME_ADDRESS2").ToString
            obj.P3_MAIL_ADDRESS2 = row("P3_MAIL_ADDRESS2").ToString
            obj.P3_LICENSE2 = row("P3_LICENSE2").ToString
            obj.P3_CITIZEN_ID2 = row("P3_CITIZEN_ID2").ToString
            obj.P3_CITIZEN_BY2 = row("P3_CITIZEN_BY2").ToString
            If Not (row("P3_CITIZEN_EXPIRED2").ToString.Trim = "") Then
                obj.P3_CITIZEN_EXPIRED2 = ConvertDate(row("P3_CITIZEN_EXPIRED2").ToString)
            End If
            If Not (row("P3_CUST_DOB2").ToString.Trim = "") Then
                obj.P3_CUST_DOB2 = ConvertDate(row("P3_CUST_DOB2").ToString)
            End If
            obj.P3_BORN_PLACE2 = row("P3_BORN_PLACE2").ToString
            obj.P3_NAME_OF_RECIEVE = row("P3_NAME_OF_RECIEVE").ToString
            obj.P3_TRAN_REASON = row("P3_TRAN_REASON").ToString
            obj.FROM_ACID = row("FROM_ACID").ToString
            obj.TO_ACID = row("TO_ACID").ToString

            obj.ISCONFIRM = 0
            obj.ISDELETE = 0
            obj.ISSEND = 0
            obj.ISSENDCONFIRM = 0
            obj.ISCOMPLETE = 0

            obj.CREATEBY = clsSessionMenuRight.getUserName
            obj.CREATEDATE = Date.Now

            'obj.UPDATEBY = clsSessionMenuRight.getUserName
            'obj.UPDATEDATE = Date.Now

            If (qry.Count = 0) Then
                AMLOEntity.AddToAMLO1059(obj)
            ElseIf (qry(0).ISSEND = "1") Then
                AMLOEntity.AddToAMLO1059(obj)
            End If

            AMLOEntity.SaveChanges()
        Next

        ScriptManager.RegisterStartupScript(Me, _
                                                Me.GetType(), _
                                                "clientScript", _
                                                "alert('อัพโหลดสำเร็จ " & dt.Rows.Count & " รายการ')", _
                                                True)
    End Sub

    Private Sub ImportFile2(ByVal fileName As String)
        Dim dt As DataTable = ReadExcel(fileName)
        AMLOEntity = New AMLO1059Entities
        Dim obj As AMLO1059_ACID_FROM
        clsSessionMenuRight = Session("ClsMenuRight")

        For Each row As DataRow In dt.Rows
            Dim acid = row("ACID").ToString.Trim
            Dim tran_date = ConvertDate(row("TRAN_DATE").ToString)

            Dim qry = (From t In AMLOEntity.AMLO1059_ACID_FROM Where t.ACID = acid _
                   And t.TRAN_DATE = tran_date).ToList
            If (qry.Count > 0) Then
                obj = qry(0)
            Else
                obj = New AMLO1059_ACID_FROM
            End If

            obj.TRAN_DATE = row("TRAN_DATE").ToString
            obj.ACID = row("ACID").ToString
            obj.FLG_TYPE = row("FLG_TYPE").ToString
            obj.SALUTATION = row("SALUTATION").ToString
            obj.CUST_FNAME = row("CUST_FNAME").ToString
            obj.CUST_MNAME = row("CUST_MNAME").ToString
            obj.CUST_LNAME = row("CUST_LNAME").ToString
            obj.NATIONALITY = row("NATIONALITY").ToString
            obj.TYPE_HOME = row("TYPE_HOME").ToString
            obj.HOME_NO = row("HOME_NO").ToString
            obj.HOME_ADDRESS = row("HOME_ADDRESS").ToString
            obj.HOME_CITY = row("HOME_CITY").ToString
            obj.HOME_STATE = row("HOME_STATE").ToString
            obj.HOME_COUNTRY = row("HOME_COUNTRY").ToString
            obj.HOME_ZIP = row("HOME_ZIP").ToString
            obj.HOME_NATION = row("HOME_NATION").ToString
            obj.TYPE_HPHONE = row("TYPE_HPHONE").ToString
            obj.HOME_PHONE = row("HOME_PHONE").ToString
            obj.TYPE_HFAX = row("TYPE_HFAX").ToString
            obj.HOME_FAX = row("HOME_FAX").ToString
            obj.OCCUPATION_CODE = row("OCCUPATION_CODE").ToString
            obj.OCCUPATION_DESC = row("OCCUPATION_DESC").ToString
            obj.TYPE_WORK = row("TYPE_WORK").ToString
            obj.WORK_NO = row("WORK_NO").ToString
            obj.WORK_ADDRESS = row("WORK_ADDRESS").ToString
            obj.WORK_CITY = row("WORK_CITY").ToString
            obj.WORK_STATE = row("WORK_STATE").ToString
            obj.WORK_COUNTRY = row("WORK_COUNTRY").ToString
            obj.WORK_ZIP = row("WORK_ZIP").ToString
            obj.WORK_NATION = row("WORK_NATION").ToString
            obj.TYPE_WPHONE = row("TYPE_WPHONE").ToString
            obj.WORK_PHONE = row("WORK_PHONE").ToString
            obj.TYPE_WFAX = row("TYPE_WFAX").ToString
            obj.WORK_FAX = row("WORK_FAX").ToString
            obj.TYPE_MAIL = row("TYPE_MAIL").ToString
            obj.MAIL_NO = row("MAIL_NO").ToString
            obj.MAIL_ADDRESS = row("MAIL_ADDRESS").ToString
            obj.MAIL_CITY = row("MAIL_CITY").ToString
            obj.MAIL_STATE = row("MAIL_STATE").ToString
            obj.MAIL_COUNTRY = row("MAIL_COUNTRY").ToString
            obj.MAIL_ZIP = row("MAIL_ZIP").ToString
            obj.MAIL_NATION = row("MAIL_NATION").ToString
            obj.TYPE_MPHONE = row("TYPE_MPHONE").ToString
            obj.MAIL_PHONE = row("MAIL_PHONE").ToString
            obj.TYPE_MCELL = row("TYPE_MCELL").ToString
            obj.MAIL_CELL = row("MAIL_CELL").ToString
            obj.TYPE_MFAX = row("TYPE_MFAX").ToString
            obj.MAIL_FAX = row("MAIL_FAX").ToString
            obj.TYPE_MMAIL = row("TYPE_MMAIL").ToString
            obj.MAIL_EMAIL = row("MAIL_EMAIL").ToString
            obj.CITIZEN_ID = row("CITIZEN_ID").ToString
            obj.CITIZEN_OPN_BY = row("CITIZEN_OPN_BY").ToString
            If Not (row("CITIZEN_OPN").ToString.Trim = "") Then
                obj.CITIZEN_OPN = ConvertDate(row("CITIZEN_OPN").ToString)
            End If
            If Not (row("CITIZEN_EXPIRED").ToString.Trim = "") Then
                obj.CITIZEN_EXPIRED = ConvertDate(row("CITIZEN_EXPIRED").ToString)
            End If

            If (qry.Count = 0) Then
                AMLOEntity.AddToAMLO1059_ACID_FROM(obj)
            End If

            AMLOEntity.SaveChanges()
        Next

        ScriptManager.RegisterStartupScript(Me, _
                                                Me.GetType(), _
                                                "clientScript", _
                                                "alert('อัพโหลดสำเร็จ " & dt.Rows.Count & " รายการ')", _
                                                True)
    End Sub

    Private Sub ImportFile3(ByVal fileName As String)
        Dim dt As DataTable = ReadExcel(fileName)
        AMLOEntity = New AMLO1059Entities
        Dim obj As AMLO1059_ACID_TO
        clsSessionMenuRight = Session("ClsMenuRight")

        For Each row As DataRow In dt.Rows
            Dim acid = row("ACID").ToString.Trim
            Dim tran_date = ConvertDate(row("TRAN_DATE").ToString)

            Dim qry = (From t In AMLOEntity.AMLO1059_ACID_TO Where t.ACID = acid _
                   And t.TRAN_DATE = tran_date).ToList
            If (qry.Count > 0) Then
                obj = qry(0)
            Else
                obj = New AMLO1059_ACID_TO
            End If

            obj.TRAN_DATE = ConvertDate(row("TRAN_DATE").ToString)
            obj.ACID = row("ACID").ToString
            obj.FLG_TYPE = row("FLG_TYPE").ToString
            obj.SALUTATION = row("SALUTATION").ToString
            obj.CUST_FNAME = row("CUST_FNAME").ToString
            obj.CUST_MNAME = row("CUST_MNAME").ToString
            obj.CUST_LNAME = row("CUST_LNAME").ToString
            obj.NATIONALITY = row("NATIONALITY").ToString
            obj.COMPANY_CODE = row("COMPANY_CODE").ToString
            obj.COMPANY_NAME = row("COMPANY_NAME").ToString
            obj.TYPE_HOME = row("TYPE_HOME").ToString
            obj.HOME_NO = row("HOME_NO").ToString
            obj.HOME_ADDRESS = row("HOME_ADDRESS").ToString
            obj.HOME_CITY = row("HOME_CITY").ToString
            obj.HOME_STATE = row("HOME_STATE").ToString
            obj.HOME_COUNTRY = row("HOME_COUNTRY").ToString
            obj.HOME_ZIP = row("HOME_ZIP").ToString
            obj.HOME_NATION = row("HOME_NATION").ToString
            obj.TYPE_HPHONE = row("TYPE_HPHONE").ToString
            obj.HOME_PHONE = row("HOME_PHONE").ToString
            obj.TYPE_HFAX = row("TYPE_HFAX").ToString
            obj.HOME_FAX = row("HOME_FAX").ToString
            obj.OCCUPATION_CODE = row("OCCUPATION_CODE").ToString
            obj.OCCUPATION_DESC = row("OCCUPATION_DESC").ToString
            obj.TYPE_WORK = row("TYPE_WORK").ToString
            obj.WORK_NO = row("WORK_NO").ToString
            obj.WORK_ADDRESS = row("WORK_ADDRESS").ToString
            obj.WORK_CITY = row("WORK_CITY").ToString
            obj.WORK_STATE = row("WORK_STATE").ToString
            obj.WORK_COUNTRY = row("WORK_COUNTRY").ToString
            obj.WORK_ZIP = row("WORK_ZIP").ToString
            obj.WORK_NATION = row("WORK_NATION").ToString
            obj.TYPE_WPHONE = row("TYPE_WPHONE").ToString
            obj.WORK_PHONE = row("WORK_PHONE").ToString
            obj.TYPE_WFAX = row("TYPE_WFAX").ToString
            obj.WORK_FAX = row("WORK_FAX").ToString
            obj.TYPE_MAIL = row("TYPE_MAIL").ToString
            obj.MAIL_NO = row("MAIL_NO").ToString
            obj.MAIL_ADDRESS = row("MAIL_ADDRESS").ToString
            obj.MAIL_CITY = row("MAIL_CITY").ToString
            obj.MAIL_STATE = row("MAIL_STATE").ToString
            obj.MAIL_COUNTRY = row("MAIL_COUNTRY").ToString
            obj.MAIL_ZIP = row("MAIL_ZIP").ToString
            obj.MAIL_NATION = row("MAIL_NATION").ToString
            obj.TYPE_MPHONE = row("TYPE_MPHONE").ToString
            obj.MAIL_PHONE = row("MAIL_PHONE").ToString
            obj.TYPE_MCELL = row("TYPE_MCELL").ToString
            obj.MAIL_CELL = row("MAIL_CELL").ToString
            obj.TYPE_MFAX = row("TYPE_MFAX").ToString
            obj.MAIL_FAX = row("MAIL_FAX").ToString
            obj.TYPE_MMAIL = row("TYPE_MMAIL").ToString
            obj.MAIL_EMAIL = row("MAIL_EMAIL").ToString
            obj.CITIZEN_ID = row("CITIZEN_ID").ToString
            obj.CITIZEN_OPN_BY = row("CITIZEN_OPN_BY").ToString
            If Not (row("CITIZEN_OPN").ToString.Trim = "") Then
                obj.CITIZEN_OPN = ConvertDate(row("CITIZEN_OPN").ToString)
            End If
            If Not (row("CITIZEN_EXPIRED").ToString.Trim = "") Then
                obj.CITIZEN_EXPIRED = ConvertDate(row("CITIZEN_EXPIRED").ToString)
            End If

            If (qry.Count = 0) Then
                AMLOEntity.AddToAMLO1059_ACID_TO(obj)
            End If

            AMLOEntity.SaveChanges()
        Next

        ScriptManager.RegisterStartupScript(Me, _
                                                Me.GetType(), _
                                                "clientScript", _
                                                "alert('อัพโหลดสำเร็จ " & dt.Rows.Count & " รายการ')", _
                                                True)
    End Sub

    Private Function GetAmlo1059Seq() As Decimal
        clsDatabase = New clsDatabase
        Dim sql As String = "select KYCADMIN.AMLO1059_S.nextval from dual"
        Dim seq As Integer = clsDatabase.QueryDataTable(sql).Rows(0)("NEXTVAL")
        Return seq
    End Function

    Public Function ConvertDate(ByVal strDate As String) As Date
        If strDate.Trim = "" Then
            Return Nothing
        Else
            Return CDate(strDate)
        End If
    End Function

    Public Function ConvertInteger(ByVal strNumber As String) As Integer
        If strNumber = "" Then
            Return Nothing
        Else
            Return CInt(strNumber)
        End If
    End Function
#End Region

End Class
